Generating Trial Balance on 1M+ postings is now 3X faster

A Practical Dilemma

In January 2023, the RBI reported a staggering volume of over 8 billion transactions processed by India's UPI network, marking the highest number since its inception in 2016. As the scale of financial transactions accelerates rapidly, the need for faster accounting practices has become an imperative to meet market demands.

However, alongside this growth, complexity surges, presenting formidable challenges when generating financial reports and closing books at the end of a fiscal year. The magnitude of time and resources required to handle these intricate accounting processes can overwhelm accounting teams, leaving them submerged in a sea of tasks.

For businesses dealing with millions of transactions, the technical complexities further compound as they grapple with vast volumes and the accuracy of financial data. Consequently, the process of closing accounts becomes even more arduous. In some cases, generating Period Closing Vouchers (PCVs) alone can take several tens of seconds.

This scenario unfolded with one of our clients in March 2023. They raised concerns about the performance of their reporting. Initially, the issue seemed linked to complications on our hosting platform. However, upon closer examination, we discovered that the reports were being prematurely terminated due to the execution of lengthy queries.

Uncovering the Bottleneck

Ever wondered why calculating account balances can take so long, even when there haven't been many changes to the ledger entries? Typically, in most integrated systems, reports and functions sift through all the previous General Ledgers (GLs), which could span several years, causing significant delays and failures for users managing a large number of GL entries.

Due to this reason, in the case of our client who had over 1M+ ledger postings, the system was unable to process them efficiently. Consequently, these extensive queries exceeded the allowed time limit, resulting in timeouts.

Addressing the Challenge

Our development team has been diligently working on tackling this challenge and has formulated an elegant solution, which lies in ERPNext's latest feature monikered as 'Accounts Closing Balance.'

This functionality utilizes a precomputing mechanism to calculate and store balances for financial statements and trial balance reports, resulting in a swift and efficient accounts closing process. By precomputing balances for specific time frames, users can benefit from a remarkable enhancement in efficiency.

To illustrate the impact of this feature, consider the example of generating a trial balance report on a site with 1.5 million GL entries. Without the feature, the execution time is approximately 21 seconds. However, with the precomputation feature enabled, the same report can be expedited in a staggering 8 seconds.

Given our client's circumstances, our team decided to implement this feature, leading to accelerated report generation and optimized resource utilization.

The time complexity of the traditional method of balance computation

The feature, seamlessly integrated with the Period Closing Voucher, captures GL Entries until the posting date of the PCV. Through the precomputation and storage of balances, financial reports and trial balances can now be generated with unprecedented speed, greatly simplifying the tasks of accounting teams. Moreover, the integrity of data is maintained as previous entries cannot be modified without canceling the associated PCV, ensuring data integrity.

Significant reduction in the time complexity after deployment of the feature

This optimization streamlines the accounting process, enabling accounting teams to allocate their time and efforts towards other essential tasks.

To summarize, with this feature, you can generate trial balance reports and perform functions at an accelerated pace, experiencing processing times up to 60% faster than before. Currently in Beta, this feature is available to all users utilizing the develop branch of ERPNext, and will be rolled out to all users in the future. We encourage you to upgrade to the latest version and elevate the efficiency of your financial statements today!

Meta Image Credits: Photo by Volkan Olmez on Unsplash